﻿// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Data.Common;

namespace net.windward.utils.ado.Excel
{
    ///<summary>
    /// A lightweight handler of functionality that the ODBC and OleDb drivers can use as well as the main implementations.
    ///</summary>
    public class WrExcelSyntax : IWrSyntax
    {

        private readonly DbProviderFactory provider;
        private readonly string odbcProvider;
        private static readonly Hashtable reservedWords = new Hashtable();

        static WrExcelSyntax()
        {
            foreach (string str in _reservedWords)
                reservedWords.Add(str.ToUpper(), str.ToUpper());
        }

        /// <summary>
        /// Create the object.
        /// </summary>
        /// <param name="provider">The provider for this connector.</param>
        /// <param name="odbcProvider">Name of the OleDb or ODBC provider.</param>
        public WrExcelSyntax(DbProviderFactory provider, string odbcProvider)
        {
            this.provider = provider;
            this.odbcProvider = odbcProvider;
        }

        /// <summary>
        /// Convert a select to normalize the "top 10" syntax
        /// </summary>
        /// <param name="select">The select with our standard usage</param>
        /// <returns>The select for this database.</returns>
        public string ConvertSelect(string select)
        {
            int pos = select.ToLower().LastIndexOf(" top ");
            if (pos == -1)
                return select;

            int posSelect = select.ToLower().IndexOf("select ");
            int posAfterTop = pos + 4;
            int posAfterTopNum = select.IndexOf(' ', posAfterTop + 1);
            if (posAfterTopNum == -1)
                posAfterTopNum = select.Length;    
            return select.Substring(0, posSelect + 7).Trim() + " " + select.Substring(pos, posAfterTopNum - pos).Trim() + " " + select.Substring(posSelect + 7, pos - posSelect - 7).Trim() + " " + select.Substring(posAfterTopNum).Trim();
        }

        /// <summary>
        /// Create a safe table name. Generally needed only if the name has a space in it or is a reserved name.
        /// </summary>
        /// <param name="tableName">The true table name.</param>
        /// <returns>The table name as it should be used in a select.</returns>
        public string SafeTableName(string tableName)
        {
            return MakeNameSafe(tableName, true);
        }

        /// <summary>
        /// Create a safe column name. Generally needed only if the name has a space in it or is a reserved name.
        /// </summary>
        /// <param name="columnName">The true column name.</param>
        /// <returns>The table name as it should be used in a select.</returns>
        public string SafeColumnName(string columnName)
        {
            return MakeNameSafe(columnName, false);
        }

        /// <summary>
        /// Create a safe table name. Generally needed only if the name has a space in it or is a reserved name.
        /// </summary>
        /// <param name="tableName">The true table name.</param>
        /// <param name="columnName">The true column name.</param>
        /// <returns>The table.column name as it should be used in a select.</returns>
        public string SafeTableColumnName(string tableName, string columnName)
        {
            return SafeTableName(tableName) + '.' + SafeColumnName(columnName);
        }

		private static string MakeNameSafe(string name, bool isTable)
		{
			if (name[0] == '[' && name[name.Length - 1] == ']')
				return name;

			bool makeSafe = reservedWords.ContainsKey(name.ToUpper());
			if (!makeSafe)
				foreach (char ch in name)
					if ((!Char.IsLetterOrDigit(ch)) && (ch != '_'))
					{
						makeSafe = true;
						break;
					}

			if (makeSafe)
			{
				if (name[0] == '[')
					return name + ']';
				if (name[name.Length - 1] == ']')
					return '[' + name;
				return '[' + name + ']';
			}

			return name;
		}

        // from http://office.microsoft.com/en-us/access/HA012315091033.aspx
        private static readonly string[] _reservedWords = { "ABSOLUTE", "ANY",
		                                                 	"ADD", "ARE",
		                                                 	"ADMINDB", "AS",
		                                                 	"ALL", "ASC",
		                                                 	"ALPHANUMERIC", "ASSERTION",
		                                                 	"ALTER", "AUTHORIZATION",
		                                                 	"AUTOINCREMENT",
		                                                 	"AND", "AVG",
		                                                 	"BEGIN", "COLLATION",
		                                                 	"BETWEEN", "COLUMN",
		                                                 	"BINARY", "COMMIT",
		                                                 	"BIT", "COMP", "COMPRESSION",
		                                                 	"BIT_LENGTH", "CONNECT",
		                                                 	"BOOLEAN", "CONNECTION",
		                                                 	"BOTH", "CONSTRAINT", "CONSTRAINTS",
		                                                 	"BY", "CONTAINER",
		                                                 	"BYTE", "CONTAINS",
		                                                 	"CASCADE", "CONVERT",
		                                                 	"CATALOG", "COUNT",
		                                                 	"CHAR", "CHARACTER", "COUNTER",
		                                                 	"CHAR_LENGTH", "CREATE",
		                                                 	"CHARACTER_LENGTH", "CURRENCY",
		                                                 	"CHECK", "CURRENT_DATE",
		                                                 	"CLOSE", "CURRENT_TIME",
		                                                 	"CLUSTERED", "CURRENT_TIMESTAMP",
		                                                 	"COALESCE", "CURRENT_USER",
		                                                 	"COLLATE", "CURSOR",
		                                                 	"DATABASE", "DISALLOW",
		                                                 	"DATE", "DISCONNECT",
		                                                 	"DATETIME", "DISTINCT",
		                                                 	"DAY", "DISTINCTROW",
		                                                 	"DEC", "DECIMAL", "DOMAIN",
		                                                 	"DECLARE", "DOUBLE",
		                                                 	"DELETE", "DROP",
		                                                 	"DESC",
		                                                 	"EQV", "FOREIGN",
		                                                 	"EXCLUSIVECONNECT", "FROM",
		                                                 	"EXEC", "EXECUTE", "CLAUSE",
		                                                 	"EXISTS", "GENERAL",
		                                                 	"EXTRACT", "GRANT",
		                                                 	"FALSE", "GROUP",
		                                                 	"FETCH", "GUID",
		                                                 	"FIRST", "HAVING",
		                                                 	"FLOAT", "FLOAT8", "HOUR",
		                                                 	"FLOAT4",
		                                                 	"IDENTITY", "INPUT",
		                                                 	"IEEEDOUBLE", "INSENSITIVE",
		                                                 	"IEEESINGLE", "INSERT",
		                                                 	"IGNORE", "INTO",
		                                                 	"IMAGE", "INT", "INTEGER", "INTEGER4",
		                                                 	"IMP", "INTEGER1",
		                                                 	"INTEGER2",
		                                                 	"IN", "INTERVAL",
		                                                 	"INDEX",
		                                                 	"INDEXCREATEDB", "IS",
		                                                 	"INNER", "ISOLATION",
		                                                 	"JOIN", "LONGTEXT",
		                                                 	"KEY", "LOWER",
		                                                 	"LANGUAGE", "MATCH",
		                                                 	"LAST", "MAX",
		                                                 	"LEFT", "MEMO",
		                                                 	"LEVEL", "MIN",
		                                                 	"LIKE", "MINUTE",
		                                                 	"LOGICAL", "LOGICAL1", "MOD",
		                                                 	"LONG", "MONEY",
		                                                 	"LONGBINARY", "MONTH",
		                                                 	"LONGCHAR",
		                                                 	"NATIONAL", "OUTER",
		                                                 	"NCHAR", "OUTPUT",
		                                                 	"NONCLUSTERED", "OWNERACCESS",
		                                                 	"NOT", "PAD",
		                                                 	"NTEXT", "PARAMETERS",
		                                                 	"NULL", "PARTIAL",
		                                                 	"NUMBER", "PASSWORD",
		                                                 	"NUMERIC", "PERCENT",
		                                                 	"NVARCHAR", "PIVOT",
		                                                 	"OCTET_LENGTH", "POSITION",
		                                                 	"OLEOBJECT", "PRECISION",
		                                                 	"ON", "PREPARE",
		                                                 	"OPEN", "PRIMARY",
		                                                 	"OPTION", "PRIVILEGES",
		                                                 	"OR", "PROC", "PROCEDURE",
		                                                 	"ORDER", "PUBLIC",
		                                                 	"REAL", "SMALLDATETIME",
		                                                 	"REFERENCES", "SMALLINT",
		                                                 	"RESTRICT", "SMALLMONEY",
		                                                 	"REVOKE", "SOME",
		                                                 	"RIGHT", "SPACE",
		                                                 	"ROLLBACK", "SQL",
		                                                 	"SCHEMA", "SQLCODE", "SQLERROR", "SQLSTATE",
		                                                 	"SECOND", "STDEV",
		                                                 	"SELECT", "STDEVP",
		                                                 	"SELECTSCHEMA", "STRING",
		                                                 	"SELECTSECURITY", "SUBSTRING",
		                                                 	"SET", "SUM",
		                                                 	"SHORT", "SYSNAME",
		                                                 	"SINGLE", "SYSTEM_USER",
		                                                 	"SIZE",
		                                                 	"TABLE", "UPDATEOWNER",
		                                                 	"TABLEID", "UPDATESECURITY",
		                                                 	"TEMPORARY", "UPPER",
		                                                 	"TEXT", "USAGE",
		                                                 	"TIME", "USER",
		                                                 	"TIMESTAMP", "USING",
		                                                 	"TIMEZONE_HOUR", "VALUE",
		                                                 	"TIMEZONE_MINUTE", "VALUES",
		                                                 	"TINYINT", "VAR",
		                                                 	"TO", "VARBINARY",
		                                                 	"TOP", "VARCHAR",
		                                                 	"TRAILING", "VARP",
		                                                 	"TRANSACTION", "VARYING",
		                                                 	"TRANSFORM", "VIEW",
		                                                 	"TRANSLATE", "WHEN",
		                                                 	"TRANSLATION", "WHENEVER",
		                                                 	"TRIM", "WHERE",
		                                                 	"TRUE", "WITH",
		                                                 	"UNION", "WORK",
		                                                 	"UNIQUE", "XOR",
		                                                 	"UNIQUEIDENTIFIER", "YEAR",
		                                                 	"UNKNOWN", "YESNO",
		                                                 	"UPDATE", "ZONE",
		                                                 	"UPDATEIDENTITY"};


    }
}